﻿using System;
using System.Text;
using System.Data;
using System.Web;

using System.IO;
using System.Threading;
using System.Data.OleDb;

namespace Vincent._Excel
{
    /// <summary>
    /// 数据导出到Excel
    /// </summary>
    public class OutputExcel
    {
        /// <summary>
        /// 将结果集导出为Excel并下载
        /// </summary>
        /// <param name="dt"> 结果集 </param>
        /// <param name="FileName"> 导出文件的文件名 </param>
        public static void DataTableToExcelAndDownload(DataTable dt, string FileName)
        {
            HttpResponse resp = HttpContext.Current.Response;
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName + ".xls");
            string colHeaders = "", ls_item = "";
            int i = 0;

            DataRow[] myRow = dt.Select("");

            //取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符 
            for (i = 0; i < dt.Columns.Count; i++)
            {
                if (i == dt.Columns.Count - 1)
                {
                    colHeaders += dt.Columns[i].Caption.ToString() + "\n";
                }
                else
                {
                    colHeaders += dt.Columns[i].Caption.ToString() + "\t";
                }
            }
            resp.Write(colHeaders);

            foreach (DataRow row in myRow)
            {
                for (i = 0; i < dt.Columns.Count; i++)
                {
                    if (i == dt.Columns.Count - 1)
                    {
                        ls_item += row[i].ToString() + "\n";
                    }
                    else
                    {
                        ls_item += row[i].ToString() + "\t";
                    }
                }

                resp.Write(ls_item);
                ls_item = "";
            }

            resp.End();
        }
    
        /// <summary>
        /// 将结果集导出为Excel并下载，且自定义字段名
        /// </summary>
        /// <param name="dt"> 由 DataSet 导出 Excel ，自定义字段名 </param>
        /// <param name="FileName"> 导出文件的文件名 </param>
        /// <param name="cells"> 字段名 </param>
        public static void DataTableToExcelAndDownload(DataTable dt, string FileName, string[] cells)
        {
            HttpResponse resp = HttpContext.Current.Response;
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName + ".xls");
            string colHeaders = "", ls_item = "";
            int i = 0;

            DataRow[] myRow = dt.Select("");

            //取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符 
            for (i = 0; i < cells.Length; i++)
            {
                if (i == cells.Length - 1)
                {
                    colHeaders += cells[i].ToString() + "\n";
                }
                else
                {
                    colHeaders += cells[i].ToString() + "\t";
                }
            }
            resp.Write(colHeaders);

            foreach (DataRow row in myRow)
            {
                for (i = 0; i < dt.Columns.Count; i++)
                {
                    if (i == dt.Columns.Count - 1)
                    {
                        ls_item += row[i].ToString() + "\n";
                    }
                    else
                    {
                        ls_item += row[i].ToString() + "\t";
                    }
                }

                resp.Write(ls_item);
                ls_item = "";
            }

            resp.End();
        }

        /// <summary>
        /// 下载服务器端的文件到本地
        /// </summary>
        /// <param name="_Request"> </param>
        /// <param name="_Response"> </param>
        /// <param name="_fileName"> 目的文件名称 </param>
        /// <param name="_fullPath"> 源文件路径 </param>
        /// <param name="_speed"> 速度大小(1024000 -> 10k/s) </param>        
        public static bool DownloadFile(HttpRequest Request, HttpResponse Response, string fileName, string fullPath, long speed)
        {
            try
            {
                FileStream myFile = new FileStream(fullPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
                BinaryReader br = new BinaryReader(myFile);
                try
                {
                    Response.AddHeader("Accept-Ranges", "bytes");
                    Response.Buffer = false;
                    long fileLength = myFile.Length;
                    long startBytes = 0;

                    double pack = 10240; //10K bytes
                    //int sleep = 200;   //每秒5次   即5*10K bytes每秒
                    int sleep = (int)Math.Floor(1000 * pack / speed) + 1;
                    if (Request.Headers["Range"] != null)
                    {
                        Response.StatusCode = 206;
                        string[] range = Request.Headers["Range"].Split(new char[] { '=', '-' });
                        startBytes = Convert.ToInt64(range[1]);
                    }
                    Response.AddHeader("Content-Length", (fileLength - startBytes).ToString());
                    if (startBytes != 0)
                    {
                        //Response.AddHeader("Content-Range", string.Format(" bytes {0}-{1}/{2}", startBytes, fileLength-1, fileLength));
                    }
                    Response.AddHeader("Connection", "Keep-Alive");
                    Response.ContentType = "application/octet-stream";
                    Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.GetEncoding("GB2312")));

                    br.BaseStream.Seek(startBytes, SeekOrigin.Begin);
                    int maxCount = (int)Math.Floor((fileLength - startBytes) / pack) + 1;

                    for (int i = 0; i < maxCount; i++)
                    {
                        if (Response.IsClientConnected)
                        {
                            Response.BinaryWrite(br.ReadBytes(int.Parse(pack.ToString())));
                            Thread.Sleep(sleep);
                        }
                        else
                        {
                            i = maxCount;
                        }
                    }
                }
                catch
                {
                    return false;
                }
                finally
                {
                    br.Close();

                    myFile.Close();
                }
            }
            catch
            {
                return false;
            }
            return true;
        }

        //使用说明，例如：
        //if (dt != null && dt.Rows.Count > 0)
        //{
        //    dt.Columns.Remove("GoodID");
        //    dt.Columns.Remove("Price_Orig");
        //    dt.Columns.Remove("Price_Lastin");
        //    dt.Columns.Remove("LastinSum");
        //    dt.Columns.Remove("GoodPic_Big");           

        //    dt.Columns[0].ColumnName = "序号";
        //    dt.Columns[1].ColumnName = "产品编号";
        //    dt.Columns[2].ColumnName = "产品名称";            
        //    dt.Columns[3].ColumnName = "产品类别";
        //    dt.Columns[4].ColumnName = "产品品牌";
        //    dt.Columns[5].ColumnName = "Vip单价";
        //    dt.Columns[6].ColumnName = "BV值";
        //    dt.Columns[7].ColumnName = "销售数量";
        //    dt.Columns[8].ColumnName = "VIP总额";
        //    dt.Columns[9].ColumnName = "BV总值";
        //    dt.Columns[10].ColumnName = "库存数量";

        //    Vincent._Excel.OutputExcel.DataTableToExcelAndDownload(dt, "XX.xls");
        //}
        //else
        //{
        //    PublicFunction.GoError(ErrorNumber.NoData, "没有数据，不能导出到Excel!", "XX");
        //}
    }
}
